
package VLSys;

import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.swing.table.*;
import javax.swing.text.*;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import DBConnection.*;
import java.sql.*;
import VLSObjects.*;

public class TransactionEdit extends JPanel {
    
    
    JTextField transactionID;
    JTextField plateNumber;
    JComboBox manufacturer;
    JComboBox model;
    JComboBox services;

    DefaultTableModel transactionsModel;
    DefaultTableModel servicesModel;
    DefaultTableModel originalModel;
    
    Calendar calendar = Calendar.getInstance();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    /*
    ImageIcon ask_red = new ImageIcon("images/prompt_ask.png");
    ImageIcon notice = new ImageIcon("images/prompt_notice.png");
    */

    ImageIcon notice_green = new ImageIcon("images/prompt_notice_green.png");
    ImageIcon ask_red = new ImageIcon("images/prompt_ask_red.png");
    ImageIcon notice_red = new ImageIcon("images/prompt_notice_red.png");

    String string[] = new String[5];
    String blank = "";
    String manufacturerSelected; 
    

    int x = -1;
    int y;
    int enable = 1;
    int enable1 = 1;
    int enable2 = 1;
    int added;

    Object headers[] = {"TransactionID", "Transaction Date", "TimeStamp", "Plate No.", "Total Amount"}; 
    Object headers2[] = {"Service", "Price"};

    int rowIndex;

    JButton getButton;
    JButton submitButton;
    JButton cancelButton;
    JButton addButton;
    JButton removeServiceButton;
    JButton editTransactionButton;

    JLabel transactionIDLabel;
    JLabel plateNumberLabel;
    JLabel manufacturerLabel;
    JLabel modelLabel;
    JLabel servicesLabel;
    JLabel newTransactionLabel;
    JLabel servicesLael;
    JLabel total;

    int z;
    int lent = 0;


    JScrollPane currentScroll;
    JTable currentTable;

    JScrollPane transactionScroll;
    JTable transactionTable;
    
    JTable originalTable;

public TransactionEdit(final JFrame frame){
    
    setVisible(false);
    setLayout (null);
    setBackground(null);
    
    DocumentFilter filter = new UppercaseDocumentFilter();
    
    transactionID = new JTextField("");
    
    plateNumber = new JTextField("");
    ((AbstractDocument) plateNumber.getDocument()).setDocumentFilter(filter);
    manufacturer = new JComboBox();
    model = new JComboBox();
    services = new JComboBox();
    
    getButton = new JButton();
    submitButton = new JButton();
    cancelButton = new JButton();
    addButton = new JButton();
    removeServiceButton = new JButton();
    editTransactionButton = new JButton();
    
    transactionIDLabel = new JLabel("Transaction ID:");
    newTransactionLabel = new JLabel ("EDIT TRANSACTION");
    plateNumberLabel = new JLabel ("Plate Number:");
    manufacturerLabel = new JLabel ("Manufacturer:");
    modelLabel = new JLabel("Model:");
    servicesLabel = new JLabel("Services:");
    total = new JLabel("Total: 0");
    
    newTransactionLabel.setFont(new Font ("sansserif", Font.PLAIN, 40));
    newTransactionLabel.setBounds(10,10,500,30);
    
    transactionIDLabel.setBounds(45,50,150,20);
    plateNumberLabel.setBounds(45,75,150,20);
    manufacturerLabel.setBounds(45,100,150,20);
    modelLabel.setBounds(45,125,150,20);
    servicesLabel.setBounds(45,150,150,20);
    total.setBounds(338,150,150,20);
    
    transactionID.setBounds(130,50,200,20);
    plateNumber.setBounds(130,75,200,20);
    manufacturer.setBounds(130,100,200,20);
    model.setBounds(130, 125, 200, 20);
    services.setBounds(130, 150, 125, 20);
    
    
    transactionID.setBorder(null);
    plateNumber.setBorder(null);
    manufacturer.setBorder(null);
    model.setBorder(null);
    
    getButton.setText("Get");
    submitButton.setText("Submit");
    cancelButton.setText("Clear");
    addButton.setText("Add");
    removeServiceButton.setText("Remove Service");
    editTransactionButton.setText("Edit"); 

    
    
    submitButton.setBounds(175,175,75,20);
    cancelButton.setBounds(255,175,75,20);
    addButton.setBounds(255, 150, 75, 20);
    editTransactionButton.setBounds(15, 390, 75, 20);
    removeServiceButton.setBounds(460, 150, 125, 20);    
    
    //table codes
    transactionTable = new JTable();
    
    transactionTable.setModel(new javax.swing.table.DefaultTableModel(null, headers
        ) {


            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return false;
            }
        });
    transactionScroll = new JScrollPane();
    transactionTable.getTableHeader().setReorderingAllowed(false);
    transactionTable.setAutoCreateRowSorter(true);
    
    transactionsModel = (DefaultTableModel)transactionTable.getModel();
            
    
    transactionScroll.setViewportView(transactionTable);
    transactionScroll.setBounds(15, 240, 575, 150);
    
    disableButton();
    
    
    transactionTable.setColumnSelectionAllowed(false);
    transactionTable.setRowSelectionAllowed(true);
    transactionTable.getTableHeader().setResizingAllowed(false);

    
    //table codes
    autoResizeColWidth(transactionTable, transactionsModel);
    
    currentTable = new JTable();
    currentTable.setModel(new javax.swing.table.DefaultTableModel(null, headers2
        ) {


            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return false;
            }
        });
    currentScroll = new JScrollPane();
    
    servicesModel = (DefaultTableModel)currentTable.getModel();
    
    currentTable.getTableHeader().setReorderingAllowed(false);
    
    
    currentScroll.setViewportView(currentTable);
    currentScroll.setBounds(338, 50, 250, 95);
    currentTable.getTableHeader().setResizingAllowed(false);
    
    autoResizeColWidth(currentTable, servicesModel);
    
    originalTable = new JTable();
    
    originalTable.setModel(new javax.swing.table.DefaultTableModel(null, headers2
        ) {


            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return false;
            }
        });
    
    originalModel = (DefaultTableModel)originalTable.getModel();
    
    
    add(transactionID);
    add(plateNumber);
    add(manufacturer);
    add(model);
    add(services);
    add(transactionIDLabel);
    add(plateNumberLabel);
    add(manufacturerLabel);
    add(modelLabel);
    add(servicesLabel);
    add(submitButton);
    add(cancelButton);
    add(addButton);
    add(removeServiceButton);
    add(editTransactionButton);
    add(newTransactionLabel);
    add(transactionScroll);
    add(currentScroll);
    add(total);
        
    
 plateNumber.addKeyListener(new KeyAdapter() {
      public void keyReleased(KeyEvent e) {
          
    
            if (e.getKeyCode()==KeyEvent.VK_BACK_SPACE)
                lent = plateNumber.getText().length() - 1; 
            else
                lent = plateNumber.getText().length() + 1; 
            
            String text = plateNumber.getText();

            if(plateNumber.getText().length()== 3 && lent == 4 )
            {       

                    plateNumber.setText(text + "-");
            }
            else 
            if(plateNumber.getText().length()== 3 && lent == 2 )
            {       

                   plateNumber.setText(plateNumber.getText().substring(0,2)); 
            }

      }

      public void keyTyped(KeyEvent e) {
      }

      public void keyPressed(KeyEvent e) {
      }
    });
    
    manufacturer.removeAllItems();
    manufacturer.addItem("");
                try{
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    int i = 1;

                    PreparedStatement pstmt = conn.prepareStatement("SELECT manufacturer FROM vehiclelist GROUP BY manufacturer");

                    ResultSet rs = pstmt.executeQuery();

                    while(rs.next())
                    {
                        manufacturer.addItem(rs.getString(1));
                    }
                    conn.close();
                }   

                catch(SQLException e)
                {
                   e.printStackTrace();
                }
            
    
    manufacturer.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {         
                
                    enableCombo();   

            }
        }
        );
    
    model.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {
                int same = 0;
                 if(model.getSelectedItem() != "" && enable != 1)
                    services.setEnabled(true);
                         
                 else if(model.getSelectedItem() == "")
                {
                    
                    services.setEnabled(false);
                    services.setSelectedItem("");
                }
         
                        services.removeAllItems();
                        services.addItem("");
                try{
                        ConnectionFactory myFactory = ConnectionFactory.getFactory();
                        Connection conn = myFactory.getConnection();

                        

                        PreparedStatement pstmt = conn.prepareStatement("SELECT servicelist.serviceName FROM vehiclelist JOIN servicelist ON vehiclelist.size = servicelist.size WHERE vehiclelist.model =  '"+ (String)model.getSelectedItem() +"' AND servicelist.available =  'Yes' ORDER BY vehiclelist.model, servicelist.serviceName");

                        ResultSet rs = pstmt.executeQuery();
                        
                        while(rs.next())
                        {
                            for(int i = 0; i <currentTable.getRowCount();i++)
                            {
                               if(rs.getString(1).equals((String)servicesModel.getValueAt(i,0)) == true)
                               {    same = 1; 
                                    
                               }
                               
                            }
                            if(same == 0)
                                services.addItem(rs.getString(1));
                            else
                                same = 0;
                        }
                        conn.close();
                    }   

                    catch(SQLException ee)
                    {
                       ee.printStackTrace();
                    }
       
             
            }
        }
        );
    
 
    
        
        transactionTable.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                TableMouseClicked(evt);
            }
            });
        
        currentTable.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                TableMouseClicked2(evt);
            }
            });
    
        editTransactionButton.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {   
                if(x != -1)
                {   
                    enableButton();
                        
                }
                else
                    JOptionPane.showMessageDialog(frame,
                                        "Please Select Transaction",
                                        "Vehicle Logging Service",
                                        JOptionPane.WARNING_MESSAGE,
                                        notice_red);
            
            }
        }
        );  
        
       submitButton.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {
                added = 1;               
                string[0] = transactionID.getText();
                string[1] = plateNumber.getText();
                string[2] = (String)manufacturer.getSelectedItem();
                string[3] = (String)model.getSelectedItem();
                string[4] = (String)services.getSelectedItem();
                
                if(string[0].length() == 0 && string[1] == blank && string[2] == blank && string[3] == blank)
                {
                    checkEmpty();
                    JOptionPane.showMessageDialog(frame,
                                        "Incomplete Input",
                                        "Vehicle Logging Service",
                                        JOptionPane.WARNING_MESSAGE,
                                        notice_red);
                    
                } 
                else if(string[0] == blank || string[1].length()== 0 || string[2] == blank || string[3] == blank)
                {
                    checkEmpty();
                    if(string[1].length()!= 0 && isPlate(plateNumber.getText()) == false)                        
                        JOptionPane.showMessageDialog(frame,
                                        "Invalid and Incomplete Input",
                                        "Vehicle Logging Service",
                                        JOptionPane.WARNING_MESSAGE,
                                        notice_red);
                   
                    else if(string[1].length() == 0 || string[1] == blank || string[2] == blank || isPlate(plateNumber.getText()) == true)
                    JOptionPane.showMessageDialog(frame,
                                        "Incomplete Input",
                                        "Vehicle Logging Service",
                                        JOptionPane.WARNING_MESSAGE,
                                        notice_red);
                    
                }    
                else if(isPlate(plateNumber.getText()) == false)
                {
                    checkEmpty();
                    JOptionPane.showMessageDialog(frame,
                                        "Invalid Input",
                                        "Vehicle Logging Service",
                                        JOptionPane.WARNING_MESSAGE,
                                        notice_red);
      
                }         
                else if(string[0] == blank || isPlate(plateNumber.getText()) == true && string[2] != blank && string[3] != blank)   
                {
                    
                    int n = JOptionPane.showConfirmDialog(
                            frame,
                            "Proceed with current values?",
                            "Vehicle Logging Service",
                            JOptionPane.YES_NO_OPTION,
                            JOptionPane.QUESTION_MESSAGE,
                                        ask_red);
                    
                    if(n==0)
                    {
                        added = n;
                        
                        Transaction editTransaction = new Transaction();
                        TransactionManager editTransactionManager = new TransactionManager();
                        ServicesPerTransaction newSPT = new ServicesPerTransaction();
                        ServicesPerTransactionManager newSPTManager = new ServicesPerTransactionManager();
                        VehicleManager newVM = new VehicleManager();
                        ServiceManager newSM = new ServiceManager();
                        
                        editTransaction.setTransactionID(transactionID.getText());
                        editTransaction.setPlateNumber(plateNumber.getText()); 
                        
                        boolean result = editTransactionManager.editTransaction(editTransaction); 
                        
                        newSPTManager.refreshServices((String)transactionID.getText());
                        
                        
                            for(int i =0; i < servicesModel.getRowCount(); i++)
                        {
                            int wow = Integer.parseInt((String)servicesModel.getValueAt(i, 1));        
                            newSPT.setTransactionID((String)transactionID.getText());
                            newSPT.setVehicleID(newVM.getVehicleID((String)model.getSelectedItem()));
                            newSPT.setServiceID(newSM.getServiceID((String)servicesModel.getValueAt(i, 0)));
                            newSPT.setServiceName((String)servicesModel.getValueAt(i, 0));
                            
                            newSPT.setAmount(wow);
                            
                            result = newSPTManager.addServicePerTransction(newSPT);
                            
                            //System.out.print(transactionID.getText() + newVM.getVehicleID((String)model.getSelectedItem()) + newSM.getServiceID((String)servicesModel.getValueAt(i, 0)) + (String)servicesModel.getValueAt(i, 0) + servicesModel.getValueAt(i, 1)) )
                           
                        }
                        
                        transactionsModel.setRowCount(0);
                        populateTable("SELECT transaction.transaction_id, transaction_date, transaction_time, plate_num, SUM(servicetransaction.amount) AS total_amount  FROM `transaction` JOIN servicetransaction ON transaction.transaction_id = servicetransaction.transaction_id WHERE transaction_date = '" + dateFormat.format(calendar.getTime()) + "' GROUP BY transaction.transaction_id ORDER BY transaction.transaction_id ASC");
                        
                        resetValues();
                        x=-1;
                        
                        if(result == true)
                        {
                            JOptionPane.showMessageDialog(frame,
                                    "Transaction Edited!",
                                    "Vehicle Logging Service",
                                    JOptionPane.WARNING_MESSAGE,
                                        notice_green);}
                        
                    }
                    
                }
            }
        }
        );
       
       removeServiceButton.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {
                int n = JOptionPane.showConfirmDialog(
                            frame,
                            "Delete Service?",
                            "Vehicle Logging Service",
                            JOptionPane.YES_NO_OPTION,
                            JOptionPane.QUESTION_MESSAGE,
                                        ask_red);
                 if(n==0)
                    {       
                            services.addItem(servicesModel.getValueAt(y, 0));
                            servicesModel.removeRow(y);
                           
                            /* try{
                                //opens db connection
                                ConnectionFactory myFactory = ConnectionFactory.getFactory();
                                Connection conn = myFactory.getConnection();

                                int i = 1;

                                //SQL Query
                                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM servicetransaction WHERE service_name = '" + servicesModel.getValueAt(y, 0) +"' AND transaction_id = " + transactionID.getText());

                                //execute query
                                services.addItem(servicesModel.getValueAt(y, 0));
                                servicesModel.setRowCount(0);
                                pstmt.executeUpdate();
                                
                                
                                
                                //close DB connection
                                conn.close();

                                }
                                catch(SQLException y)
                                {
                                   y.printStackTrace();
                                }
                            
                                try{
                                ConnectionFactory myFactory = ConnectionFactory.getFactory();
                                Connection conn = myFactory.getConnection();

                                int i = 1;

                                servicesModel.getDataVector().removeAllElements();
                                currentTable.setAutoCreateRowSorter(true);

                                PreparedStatement pstmt = conn.prepareStatement("SELECT service_name, amount FROM servicetransaction, transaction WHERE transaction.transaction_id = servicetransaction.transaction_id AND transaction.transaction_id = "+ transactionID.getText());

                                ResultSet rs = pstmt.executeQuery();

                                while(rs.next())
                                {  
                                    servicesModel.addRow(new Object[]{rs.getString(1), rs.getString(2)});
                                    servicesModel.fireTableDataChanged();
                                }
                                conn.close();
                                }   

                                catch(SQLException d)
                                {
                                   d.printStackTrace();
                                } */
                                
                        int tot = 0;
                        
                        for(int i = 0; i<currentTable.getRowCount(); i++)
                        {
                                int wow = Integer.parseInt((String)servicesModel.getValueAt(i, 1));
                            tot = tot + wow;
                        }
                        total.setText("Total: "+tot); 
                            
                    }
                    
             
            }
        }
        );
       
       addButton.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {
                if(services.getSelectedItem() == blank)
                {
                    JOptionPane.showMessageDialog(frame,
                                    "Please select Service to Add!",
                                    "Vehicle Logging Service",
                                    JOptionPane.WARNING_MESSAGE,
                                        notice_red);
                }
                else
                {   int n = JOptionPane.showConfirmDialog(
                            frame,
                            "Add Service?",
                            "Vehicle Logging Service",
                            JOptionPane.YES_NO_OPTION,
                            JOptionPane.QUESTION_MESSAGE,
                                        ask_red);
                 if(n==0)
                    {
                        
                       try{
                                ConnectionFactory myFactory = ConnectionFactory.getFactory();
                                Connection conn = myFactory.getConnection();

                                int i = 1;

                                PreparedStatement pstmt = conn.prepareStatement("SELECT servicelist.serviceName, servicelist.service_amount FROM vehiclelist JOIN servicelist ON vehiclelist.size = servicelist.size WHERE vehiclelist.model = '"+ (String)model.getSelectedItem() +"' AND servicelist.serviceName = '"+ (String)services.getSelectedItem() +"' ORDER BY vehiclelist.model, servicelist.serviceName");

                                ResultSet rs = pstmt.executeQuery();

                                while(rs.next())
                                {
                                    servicesModel.addRow(new Object[]{rs.getString(1), rs.getString(2)});
                                }
                                conn.close();
                                
                                services.removeItem((String)services.getSelectedItem());
                                services.setSelectedItem("");
                            }   

                       catch(SQLException ee)
                            {
                               ee.printStackTrace();
                            }
                       
                        int tot = 0;
                        
                        for(int i = 0; i<currentTable.getRowCount(); i++)
                        {
                            int wow = Integer.parseInt((String)servicesModel.getValueAt(i, 1));
                            tot = tot + wow;
                        }
                        total.setText("Total: "+tot);                             
                        JOptionPane.showMessageDialog(frame,
                                    "Service Added",
                                    "Vehicle Logging Service",
                                    JOptionPane.WARNING_MESSAGE,
                                        notice_green);
                    }
                    
                }
             
            }
        }
        );
       
       cancelButton.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {
                 int n = JOptionPane.showConfirmDialog(
                            frame,
                            "Are you sure you want to clear the values?",
                            "Vehicle Logging Service",
                            JOptionPane.YES_NO_OPTION,
                            JOptionPane.QUESTION_MESSAGE,
                                        ask_red);
               if(n==0)
                    { 
                        Transaction editTransaction = new Transaction();
                        TransactionManager editTransactionManager = new TransactionManager();
                        ServicesPerTransaction newSPT = new ServicesPerTransaction();
                        ServicesPerTransactionManager newSPTManager = new ServicesPerTransactionManager();
                        VehicleManager newVM = new VehicleManager();
                        ServiceManager newSM = new ServiceManager();
                        
                        editTransaction.setTransactionID(transactionID.getText());
                        editTransaction.setPlateNumber(plateNumber.getText()); 
                        
                        boolean result = editTransactionManager.editTransaction(editTransaction); 
                        
                        newSPTManager.refreshServices((String)transactionID.getText());
                        
                        
                            for(int i =0; i < originalModel.getRowCount(); i++)
                        {
                            int wow = Integer.parseInt((String)originalModel.getValueAt(i, 1));        
                            newSPT.setTransactionID((String)transactionID.getText());
                            newSPT.setVehicleID(newVM.getVehicleID((String)model.getSelectedItem()));
                            newSPT.setServiceID(newSM.getServiceID((String)originalModel.getValueAt(i, 0)));
                            newSPT.setServiceName((String)originalModel.getValueAt(i, 0));
                            
                            newSPT.setAmount(wow);
                            
                            result = newSPTManager.addServicePerTransction(newSPT);
                        }
                            
                        transactionsModel.setRowCount(0);
                        populateTable("SELECT transaction.transaction_id, transaction_date, transaction_time, plate_num, SUM(servicetransaction.amount) AS total_amount  FROM `transaction` JOIN servicetransaction ON transaction.transaction_id = servicetransaction.transaction_id WHERE transaction_date = '" + dateFormat.format(calendar.getTime()) + "' GROUP BY transaction.transaction_id ORDER BY transaction.transaction_id ASC");
                        
                resetValues();
                disableButton();
                x=-1;
                servicesModel.getDataVector().removeAllElements();
                servicesModel.fireTableDataChanged();
                
                
                    }
               
            }
        }
        );
       
       
    }
    
    public void disableButton()
    {  
        transactionID.setEditable(false);
        transactionID.setEnabled(false);
        plateNumber.setEnabled(false);
        manufacturer.setEnabled(false);
        model.setEnabled(false);
        services.setEnabled(false);
        submitButton.setEnabled(false);
        addButton.setEnabled(false);
        cancelButton.setEnabled(false);
        removeServiceButton.setEnabled(false);
        editTransactionButton.setEnabled(true);
        enable = 1;
        enable1 = 0;


   }  
    
        public void enableButton()
        {  
                plateNumber.setEnabled(true);
                manufacturer.setEnabled(true);
                model.setEnabled(true);
                services.setEnabled(true);
                submitButton.setEnabled(true);
                addButton.setEnabled(true);             
                removeServiceButton.setEnabled(true);
                cancelButton.setEnabled(true);
                editTransactionButton.setEnabled(false);
                enable = 0;
                enable1 = 1;


        }
        
   public void checkEmpty()
    {   
        String s = "";
       
        if(plateNumber.getText().length() == 0)
            plateNumber.setBackground(Color.YELLOW);
        else if(isPlate(plateNumber.getText()) == false)
            plateNumber.setBackground(Color.RED);
        
        if(isPlate(plateNumber.getText()) == true)
            plateNumber.setBackground(null);
        
        if((String)manufacturer.getSelectedItem() == s)
            manufacturer.setBackground(Color.YELLOW);
        
        if((String)model.getSelectedItem() == s)
            model.setBackground(Color.YELLOW);
        
        if((String)manufacturer.getSelectedItem() != s)
            manufacturer.setBackground(null);
        
        if((String)model.getSelectedItem() != s)
            model.setBackground(null);
        
    }
     
   public boolean isPlate(String x)
    {
        char[] check = x.toCharArray();        
        if(check.length == 7)
            {
            if(Character.isLetter(check[0]) && Character.isLetter(check[1]) && Character.isLetter(check[2]) && check[3] == '-' && Character.isDigit(check[4]) && Character.isDigit(check[5]) && Character.isDigit(check[6]))
            {       

                return true;
            }         
            else
                return false;
            }
        else
           return false;
    }
     
   private void TableMouseClicked(java.awt.event.MouseEvent evt) 
   {                                     
       int rowSelected = transactionTable.getSelectedRow();
       
       
       if(enable == 1)
       {
       x = rowSelected;
       
       transactionID.setText(Integer.toString((Integer)transactionsModel.getValueAt(x, 0)));
       plateNumber.setText((String)transactionsModel.getValueAt(x, 3));
       
       
       try{
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();


                    PreparedStatement pstmt = conn.prepareStatement("SELECT vehiclelist.manufacturer, vehiclelist.model FROM vehiclelist, servicetransaction, transaction WHERE transaction.transaction_id = servicetransaction.transaction_id AND vehiclelist.vehicleID = servicetransaction.vehicle_id AND transaction.transaction_id = " + transactionID.getText());
       
                    ResultSet rs = pstmt.executeQuery();

                    while(rs.next())
                    {  
                        manufacturer.setSelectedItem(rs.getString(1));
                        model.setSelectedItem(rs.getString(2));
                        
                        
                    }
                    conn.close();
                }   

                catch(SQLException e)
                {
                   e.printStackTrace();
                }
       
       
            try{
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    int i = 1;
                    
                    servicesModel.getDataVector().removeAllElements();
                    currentTable.setAutoCreateRowSorter(true);

                    PreparedStatement pstmt = conn.prepareStatement("SELECT service_name, amount FROM servicetransaction, transaction WHERE transaction.transaction_id = servicetransaction.transaction_id AND transaction.transaction_id = "+ transactionID.getText() + " ORDER BY servicetransaction.service_name");

                    ResultSet rs = pstmt.executeQuery();

                    while(rs.next())
                    {  
                        servicesModel.addRow(new Object[]{rs.getString(1), rs.getString(2)});
                        servicesModel.fireTableDataChanged();
                        
                        originalModel.addRow(new Object[]{rs.getString(1), rs.getString(2)});
                        originalModel.fireTableDataChanged();
                    }
                    conn.close();
                }   

                catch(SQLException e)
                {
                   e.printStackTrace();
                }
       
                int same = 0;
                 
                
                        services.removeAllItems();
                        services.addItem("");
                try{
                        ConnectionFactory myFactory = ConnectionFactory.getFactory();
                        Connection conn = myFactory.getConnection();

                        

                        PreparedStatement pstmt = conn.prepareStatement("SELECT servicelist.serviceName FROM vehiclelist JOIN servicelist ON vehiclelist.size = servicelist.size WHERE vehiclelist.model =  '"+ (String)model.getSelectedItem() +"' AND servicelist.available =  'Yes' ORDER BY vehiclelist.model, servicelist.serviceName");

                        ResultSet rs = pstmt.executeQuery();
                        
                        while(rs.next())
                        {
                            for(int i = 0; i <currentTable.getRowCount();i++)
                            {
                               if(rs.getString(1).equals((String)servicesModel.getValueAt(i,0)) == true)
                               {    same = 1; 
                                    
                               }
                               
                            }
                            if(same == 0)
                                services.addItem(rs.getString(1));
                            else
                                same = 0;
                        }
                        conn.close();
                    }   

                    catch(SQLException ee)
                    {
                       ee.printStackTrace();
                    }
                
                    int tot = 0;
                        for(int i = 0; i<currentTable.getRowCount(); i++)
                        {

                            tot = tot + Integer.parseInt((String)servicesModel.getValueAt(i, 1));
                        }
                        total.setText("Total: "+tot);
       

       }
        
    }
   
   private void TableMouseClicked2(java.awt.event.MouseEvent evt) 
   {                                     
       int rowSelected = currentTable.getSelectedRow();
       
       if(enable1 == 1)
       {
            y = rowSelected;
       }
       
        
    }
       
   class UppercaseDocumentFilter extends DocumentFilter {
        public void insertString(DocumentFilter.FilterBypass fb, int offset,String text, AttributeSet attr)

        throws BadLocationException {



        fb.insertString(offset, text.toUpperCase(), attr);

        }
        public void replace(DocumentFilter.FilterBypass fb, int offset, int length,String text, AttributeSet attrs)

        throws BadLocationException {

        fb.replace(offset, length, text.toUpperCase(), attrs);

        }
    }

   
   public void resetValues()
    {
        plateNumber.setBackground(null);
        manufacturer.setBackground(null);
        model.setBackground(null);
        services.setBackground(null);
        
        disableButton();        

                
        
        plateNumber.setText("");
        manufacturer.setSelectedItem("");
        model.setSelectedItem("");
        services.setSelectedItem("");
       
    }
   
   public void enableCombo()
    {
        if(manufacturer.getSelectedItem() != "")
        {
            model.removeAllItems();
            model.addItem("");
            
            manufacturerSelected = (String)manufacturer.getSelectedItem();
            
                try{
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    int i = 1;

                    PreparedStatement pstmt = conn.prepareStatement("SELECT model FROM vehiclelist WHERE manufacturer = ?");

                    pstmt.setString(i++, manufacturerSelected);

                    ResultSet rs = pstmt.executeQuery();

                    while(rs.next())
                    {  
                        model.addItem(rs.getString(1));
                    }
                    conn.close();
                }   

            catch(SQLException e)
            {
               e.printStackTrace();
            }
            
        }
                
        else if(manufacturer.getSelectedItem() == "")
        {
            model.setEnabled(false);
            services.setEnabled(false);
            model.setSelectedItem("");
            services.setSelectedItem("");
        }
        
        if(enable !=1) 
            model.setEnabled(true);
        
    }
   
   private JTable autoResizeColWidth(JTable table, DefaultTableModel model) {
        table.setAutoResizeMode(JTable.AUTO_RESIZE_LAST_COLUMN);
        table.setModel(model);

        int margin = 3;

        for (int i = 0; i < table.getColumnCount()-1; i++) {
            int                     vColIndex = i;
            DefaultTableColumnModel colModel  = (DefaultTableColumnModel) table.getColumnModel();
            TableColumn             col       = colModel.getColumn(vColIndex);
            int                     width     = 0;

            // Get width of column header
            TableCellRenderer renderer = col.getHeaderRenderer();

            if (renderer == null) {
                renderer = table.getTableHeader().getDefaultRenderer();
            }

            Component comp = renderer.getTableCellRendererComponent(table, col.getHeaderValue(), false, false, 0, 0);

            width = comp.getPreferredSize().width;

            // Get maximum width of column data
            for (int r = 0; r < table.getRowCount(); r++) {
                renderer = table.getCellRenderer(r, vColIndex);
                comp     = renderer.getTableCellRendererComponent(table, table.getValueAt(r, vColIndex), false, false,
                        r, vColIndex);
                width = Math.max(width, comp.getPreferredSize().width);
            }

            // Add margin
            width += 2 * margin;

            // Set the width
            col.setPreferredWidth(width);
        }

        ((DefaultTableCellRenderer) table.getTableHeader().getDefaultRenderer()).setHorizontalAlignment(
            SwingConstants.LEFT);

        // table.setAutoCreateRowSorter(true);
        table.getTableHeader().setReorderingAllowed(false);

        return table;
    }
   
   public void populateTable(String pS){
         
              try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query
                    PreparedStatement pstmt = conn.prepareStatement(pS);

                    //execute query
                    ResultSet rs = pstmt.executeQuery();
                    
                    transactionsModel.getDataVector().removeAllElements();
                    transactionTable.setAutoCreateRowSorter(true);

                    
                    //populate JTable 
                    while(rs.next()){
                    transactionsModel.addRow(new Object[]{rs.getInt(1), rs.getDate(2), rs.getTime(3), rs.getString(4), rs.getInt(5)});
                    transactionsModel.fireTableDataChanged();
                    }
                    
                    //close DB connection
                    conn.close();
                }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                   System.out.print("ayaw!");
                }
             
          }
}
